<?php
$sql[] = "CREATE TABLE IF NOT EXISTS `t_categories` (`id` INTEGER PRIMARY KEY  DEFAULT '', `name` VARCHAR DEFAULT '', `color` VARCHAR DEFAULT '', `maxAmountPerMonth` INTEGER DEFAULT '0');";

$sql[] = "CREATE TABLE IF NOT EXISTS `t_items` (`id` INTEGER PRIMARY KEY  DEFAULT '', `amount` FLOAT DEFAULT '', `operationDate` DATETIME DEFAULT '', `payee` VARCHAR DEFAULT '', `memo` VARCHAR DEFAULT '', `cleared` VARCHAR DEFAULT '', `category` INTEGER DEFAULT '', `createDate` DATETIME DEFAULT '', `debit` INTEGER DEFAULT '', `checkSum` VARCHAR DEFAULT '', `comments` VARCHAR DEFAULT '', `deleteFlag` INTEGER DEFAULT 0, `investmentFlag` INTEGER DEFAULT 0);";

$sql[] = "CREATE TABLE IF NOT EXISTS `t_keywords` (`id` INTEGER PRIMARY KEY  DEFAULT '', `keyword` VARCHAR DEFAULT '', `category` INTEGER DEFAULT '');";

$sql[] = "CREATE TABLE IF NOT EXISTS `t_settings` (`id` INTEGER PRIMARY KEY  DEFAULT '' ,`language` VARCHAR DEFAULT '' ,`username` VARCHAR DEFAULT '' ,`password` VARCHAR DEFAULT '' ,`secureAccess` INTEGER DEFAULT '' ,`setupDone` INTEGER DEFAULT '' ,`currency` VARCHAR DEFAULT '' , `cb_version` VARCHAR DEFAULT '0.7.1', `range` VARCHAR DEFAULT '', `limitMonth` VARCHAR DEFAULT '');";

// new as of 0.7.1
$sql[] = "CREATE TABLE IF NOT EXISTS `t_version` (`id` INTEGER PRIMARY KEY  DEFAULT '' ,`cb_version` VARCHAR DEFAULT '0.7.1');";

// deprecated as of 0.7.1
// $sql[6] = "CREATE VIEW IF NOT EXISTS 'v_all_month' AS SELECT distinct strftime(\"%m-%Y\", operationDate) as month FROM t_items ORDER BY month DESC;";

// new as of 0.7.1
$sql[] = "CREATE VIEW IF NOT EXISTS 'v_distinct_month' AS SELECT distinct strftime(\"%Y-%m-01\", operationDate) as month FROM t_items ORDER BY operationDate DESC;";

$sql[] = "CREATE VIEW IF NOT EXISTS 'v_categories' AS select * from t_categories ORDER BY name ASC;";

$sql[] = "CREATE VIEW IF NOT EXISTS 'v_items' AS  SELECT t_items.id as id, amount, debit, payee, memo, operationDate, category, t_categories.name as categoryName, comments, deleteFlag, investmentFlag FROM t_items JOIN t_categories on t_items.category = t_categories.id order by date(operationDate) DESC;";

$sql[] = "CREATE VIEW IF NOT EXISTS 'v_items_unfiled' AS SELECT t_items.id as id, amount, debit, payee, memo, operationDate, category, comments, t_categories.name as categoryName, comments FROM t_items JOIN t_categories on t_items.category = t_categories.id WHERE category = \"1\" order by date(operationDate) DESC;";

$sql[] = "CREATE VIEW IF NOT EXISTS 'v_settings' AS SELECT * FROM t_settings LIMIT 0,1;";

$sql[] = "CREATE VIEW IF NOT EXISTS 'v_total_amount' AS  SELECT count(amount) as count, total(amount) as total, strftime(\"%m\", operationDate) as month, strftime(\"%Y\", operationDate) as year, debit, category, name, color, maxAmountPerMonth, strftime(\"%m-%Y\", operationDate) as limitMonth, operationDate FROM t_items JOIN t_categories ON t_categories.id=t_items.category GROUP BY debit, category, strftime(\"%m\", operationDate) ORDER BY operationDate ASC;";

$sql[] = "CREATE UNIQUE INDEX IF NOT EXISTS `i_items` ON `t_items` (`checkSum` ASC);";

$sql[] = "CREATE UNIQUE INDEX IF NOT EXISTS `i_keyword` ON `t_keywords` (`keyword` ASC);";

// new as of 0.7.1
$sql[] = "CREATE UNIQUE INDEX IF NOT EXISTS `i_category` ON `t_categories` (`name` ASC);";

// new as of 0.7.1
$sql[] = "INSERT INTO t_version (`cb_version`) VALUES ('0.7.1');";

$sql[] = "INSERT INTO t_categories (`name`,`color`) VALUES ('uncategorized', '990000');";
?>